/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.edu.seu.cose.hobbysharing.dao;

import cn.edu.seu.cose.hobbysharing.bean.Hobby;
import cn.edu.seu.cose.hobbysharing.bean.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.LinkedList;

/**
 *
 * @author Yan
 */
public class HobbyDao {
    private ConnectionFactory f ;
    private Hobby getHobbyByResultSet (ResultSet rs) throws SQLException{
        long hobby_id = rs.getLong("hobby_id");
        String hobby_name = rs.getString("hobby_name");
        Hobby hobby = new Hobby();
        hobby.setHobbyId(hobby_id);
        hobby.setHobbyName(hobby_name);
        return hobby;
    }
    public HobbyDao (){
        f = new ConnectionFactory ();
    }
    public Collection <User> getUsersWithMostCommonHobbiesWhoIsNotAFriend(String user_uuid, long size)
        throws SQLException {
        Connection c =  f.getConnection();
        try{
            PreparedStatement ps =c.prepareStatement(
                    "SELECT user_uuid, COUNT(1) as count FROM ("
                    + "SELECT hobby_id FROM hs_user_hobby_relationship WHERE user_uuid=1) a,"
                    + "hs_user_hobby_relationship b, "
                    + "(SELECT friend_uuid FROM hs_user_friend_relationship WHERE user_uuid= ?) c "
                    + "WHERE a.hobby_id=b.hobby_id AND b.user_uuid<>1 AND b.user_uuid<>c.friend_uuid "
                    + "GROUP BY user_uuid ORDER BY COUNT(1) DESC LIMIT ?;"
                   );
            ps.setString(1, user_uuid);
            ps.setLong(2, size);
            ResultSet rs = ps.executeQuery();
            Collection<User> users = new LinkedList<User>();
            UserDao ud = new UserDao();
            while(rs.next()){
                 users.add(ud.getUserByID(rs.getString("user_uuid")));
            }
            return users;
        }finally {
            c.close();
        }
    }
    public Collection<Hobby> getHobbiesByUser(String user_uuid) throws SQLException {
        Connection c =  f.getConnection();
        try{
        PreparedStatement ps =c.prepareStatement("select * from hs_user_hobby_relationship where useruuid = ?");
        ps.setString(1, user_uuid);
        ResultSet rs = ps.executeQuery();
        Collection<Hobby> hobbies = new LinkedList<Hobby>();
        while(rs.next()){
            hobbies.add(getHobbyByResultSet(rs));
        }
        return hobbies;
        
        }finally {
            c.close();
        }
    }
    public Collection<String> getUserIDsByHobby(long hobby_id) throws SQLException{
        Connection c = f.getConnection();
        PreparedStatement ps = c.prepareStatement("select * from hs_user_hobby_relationship where hobby_id = ?");
        ps.setLong(1,hobby_id);
        ResultSet rs = ps.executeQuery();
        Collection<String> userIDs = new LinkedList<String>();
        while(rs.next()){
            userIDs.add(rs.getString("user_uuid"));
        }
        return userIDs;
    }
    public Hobby getHobbyByID (long hobby_id) throws SQLException{
        Connection c =  f.getConnection();
        try{
        PreparedStatement ps =c.prepareStatement("select * from hs_hobbies where hobby_id = ?");
        ps.setLong(1, hobby_id);
        ResultSet rs = ps.executeQuery();
        if (!rs.next()){
            return null;
        }
        return getHobbyByResultSet(rs);
        }finally {
            c.close();
        }
    }
    public long getHobbyID (String hobby_name) throws SQLException{
        Connection c =  f.getConnection();
        try{
        PreparedStatement ps =c.prepareStatement("select * from hs_hobbies where hobby_name = ?");
        ps.setString(1, hobby_name);
        ResultSet rs = ps.executeQuery();
        if (!rs.next()){
            return -1;
        }
        return getHobbyByResultSet(rs).getHobbyId();
        }finally {
            c.close();
        }
    }
    public Collection<Hobby> getAllHobbies() throws SQLException{
        Connection c =f.getConnection();
        try {
        PreparedStatement ps =c .prepareStatement("select * from hs_hobbies");
        //ps.setString(1, );
        ResultSet rs = ps.executeQuery();
        Collection <Hobby> hobbies = new LinkedList<Hobby>();
        while (rs.next()){
            hobbies.add(getHobbyByResultSet(rs));
        }
        return hobbies;
        }finally {
            c.close();
        }
     }
    public Collection<Hobby> getRandomHobbies (long number) throws SQLException{
        Connection c =f.getConnection();
        try {
        PreparedStatement ps =c .prepareStatement("select * from hs_hobbies order by rand() limit ?");
        //ps.setString(1, );
        ps.setLong(1, number);
        ResultSet rs = ps.executeQuery();
        Collection <Hobby> hobbies = new LinkedList<Hobby>();
        while (rs.next()){
            hobbies.add(getHobbyByResultSet(rs));
        }
        return hobbies;
        }finally {
            c.close();
        }
        
    }
    public void addHobby (String user_uuid,long hobby_id) throws SQLException{
        Connection c = f.getConnection();
        try {
        PreparedStatement ps = c.prepareStatement("insert into hs_user_hobby_relationship(user_uuid,hobby_id) values (?,?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, user_uuid);
        ps.setLong(2,hobby_id);
        ps.executeUpdate();
        }finally {
            c.close();
        }
    }    
    public void removeHobby (String user_uuid,long hobby_id) throws SQLException{
        Connection c = f.getConnection();
        try {
        PreparedStatement ps = c.prepareStatement("delete from hs_user_hobby_relationship where (user_uuid,hobby_id) values (?,?)");
        ps.setString(1, user_uuid);
        ps.setLong(2,hobby_id);
        ps.executeUpdate();
        }finally {
            c.close();
        }
    }
    public long countUsersByHobby(long hobby_id) throws SQLException{
        Connection c = f.getConnection();
        PreparedStatement ps = c.prepareStatement("select count(*) from hs_user_hobby_relationship where hobby_id = ?");
        ps.setLong(1,hobby_id);
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            return rs.getLong(1);
        } else {
            throw new SQLException("countUsersByHobby failed.");
        }
    }
    public void deleteHobby (long hobby_id) throws SQLException {
        Connection c = f.getConnection();
           try {
           PreparedStatement ps = c.prepareStatement("delete from hs_hobbies where hobby_id =?");
           ps.setLong(1, hobby_id);
          // ps.setLong(2,hobby_id);
           ps.executeUpdate();
           }finally{
            c.close();
           
           }
        
    }
    public long newHobby (String hobby_name) throws SQLException{
        Connection c = f.getConnection();
        try {
        PreparedStatement ps = c.prepareStatement("insert into hs_hobbies(hobby_name) values (?)",
                Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, hobby_name);
        int rowCount = ps.executeUpdate();
        if (rowCount <=0){
            return -1;
        }
        ResultSet rs = ps.getGeneratedKeys();
        if (!rs.next()){
            return -1;
        }
        return rs.getLong(1);
        }finally {
            c.close();
        }
    }

    public void editHobby (long hobby_id,String hobby_name) throws SQLException{
        Connection c = f.getConnection();
        try {
        PreparedStatement ps = c.prepareStatement("update hs_hobbies set hobby_id=?,hobby_name=? where hobby_id =?");
        ps.setLong(1, hobby_id);
        ps.setString(2, hobby_name);
        ps.executeUpdate();
        }finally {
            c.close();
        }
    } 
    
}
